-- @owner: wan005
-- @date: 2024/10/8
-- @testpoint: pipelined函数返回嵌套表类型，其中嵌套顺序数组类型，部分合理报错
-- @modify: modified by zhangbingliang at 2025.01,优化用例适配代码变更
--pre1:切换A库执行
@conn PrimaryDbAmode;

--step1:创建嵌套表类型，其中嵌套顺序数组类型;expect:成功
create type varray_type_0004_1 as varray(10) of numeric;
create type varray_type_0004_2 as varray(10) of integer;
create type varray_type_0004_3 as varray(10) of boolean;
create type varray_type_0004_4 as varray(10) of date;
create type varray_type_0004_5 as varray(10) of time;
create type varray_type_0004_6 as varray(10) of varchar(200);
create or replace type type_0004 as (c1 varray_type_0004_1,
c2 varray_type_0004_2, c3 varray_type_0004_3, c4 varray_type_0004_4,
c5 varray_type_0004_5, c6 varray_type_0004_6);
create or replace type tb_type_0004 as table of type_0004;

--step2:创建管道函数并查看返回值;expect:失败
create or replace function func_pipelined_0004(count in number)
returns tb_type_0004 pipelined
as $$
declare result type_0004;
begin
    for i in 1 .. count loop
        result.c1 := array[1, -2, 0, -0, 6];
        pipe row(result.c1[i]);
        result.c2 := array[1, -2, 0, -0, 100]
        pipe row(result.c2[i]);
        result.c3 := array[1, 0, True, False, 0]
        pipe row(result.c3[i]);
        result.c4 := array[20220101, '2022-01-01', date'2022-01-01', 20240101, date'2022-01-01']
        pipe row(result.c4[i]);
        result.c5 := array[111111, '11:11:11', time'11:11:11.666', time'11:11:11', time'23:59:59']
        pipe row(result.c5[i]);
        result.c6 := array[111111, '11:11:11', 'sdfas@#$%%@#$', time'11:11:11', 'sdfgwRFDS']
        pipe row(result.c6[i]);
        pipe row(result);
    end loop;
return;
end;
$$ language plpgsql;
/

--step3:调用pipelined函数并查看返回类型;expect:失败
select func_pipelined_0004(5);
select * from func_pipelined_0004(5);
select * from table(func_pipelined_0004(5));
select c1 from table(func_pipelined_0004(5));
select pg_typeof(func_pipelined_0004(5));

--step4:使用alter修改函数名称;expect:失败
alter function func_pipelined_0004(count in number) rename to func_pipelined_0004_1;
select func_pipelined_0004_1(5);
select * from func_pipelined_0004_1(5);
select * from table(func_pipelined_0004_1(5));

--step5:清理环境;expect:成功
drop type if exists tb_type_0004 cascade;
drop type if exists type_0004 cascade;
drop type if exists varray_type_0004_1 cascade;
drop type if exists varray_type_0004_2 cascade;
drop type if exists varray_type_0004_3 cascade;
drop type if exists varray_type_0004_4 cascade;
drop type if exists varray_type_0004_5 cascade;
drop type if exists varray_type_0004_6 cascade;
drop function if exists func_pipelined_0004_1;
